﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Runtime.Serialization;
using System.ServiceModel;
using System.Text;
using IToursDataService;
using System.Data;
using Microsoft.Practices.EnterpriseLibrary.Data;
using System.Data.Common;
using IToursDataService.Models;

namespace ToursDataService
{
    // 注意: 使用“重构”菜单上的“重命名”命令，可以同时更改代码、svc 和配置文件中的类名“LocalTeamGroupCtrl”。
    public class LocalTeamGroupCtrl : ILocalTeamGroupCtrl
    {
        /// <summary>
        /// 拷贝属于龙润旅行社的行程计划到本地行程计划中
        /// </summary>
        /// <param name="scheduleIds">以‘|’分割的行程计划ID</param>
        /// <returns></returns>
        public int CopyTourGroupSchedule2LocalSchedule(string scheduleIds, string ownerProviderId)
        {
            string[] scheduleIdArray = scheduleIds.Split(new char[] { '|' });

            StringBuilder Sql = new StringBuilder();
            Sql.AppendFormat(@"insert into Sch_ProvidersPlanSchedule
                                        ([TourGroupScheduleID],[TourgroupID], [Date],[Time], [StayOver], [Journeytype], [StartCity], [DestinationCity], [TrafficType], [Traffic], [TicketType],[TravelAgencyID], [TravelAgencyName], [Reception], [Context], [Hotel],[HotelID],[Seat],[PageNumber],[OwnerProviderID])
                                        select [TourGroupScheduleID],[TourgroupID], [Date],[Time], [StayOver], [Journeytype], [StartCity], [DestinationCity], [TrafficType], [Traffic], [TicketType],[TravelAgencyID], [TravelAgencyName], [Reception], [Context], [Hotel],[HotelID],[Seat],1,'{0}' from orgt_TourGroupSchedule ", ownerProviderId);//去处了‘用餐列’[Dining]

            Sql.Append(" WHERE TourGroupScheduleID=");
            for (int i = 1; i < scheduleIdArray.Length; i++)
            {
                Sql.AppendFormat("{0} ", scheduleIdArray[i]);
                if (i != scheduleIdArray.Length - 1)
                    Sql.AppendFormat(" or tourgroupscheduleid=");
            }

            Database db = DatabaseFactory.CreateDatabase();

            return db.ExecuteNonQuery(CommandType.Text, Sql.ToString());
        }
        public DataTable GetLocalPlanSchedule(string tourGroupID, string loginProviderID, int pageNumber)
        {
            Database db = DatabaseFactory.CreateDatabase();
            string sql = string.Format("SELECT * FROM Sch_ProvidersPlanSchedule WHERE TourGroupID='{0}' AND OwnerProviderID='{1}' AND PageNumber={2} ORDER BY [Date],[Time]", tourGroupID, loginProviderID, pageNumber);
            DbCommand dbCommand = db.GetSqlStringCommand(sql);

            DataSet ds = db.ExecuteDataSet(dbCommand);
            if (ds.Tables.Count > 0)
                return ds.Tables[0];
            else
                return null;
        }
        public DataSet GetLocalPlanSchedule(string tourGroupID, string loginProviderID)
        {
            Database db = DatabaseFactory.CreateDatabase();
            string sql = string.Format("SELECT * FROM Sch_ProvidersPlanSchedule WHERE TourgroupID='{0}'  AND OwnerProviderID='{1}'", tourGroupID, loginProviderID);

            return db.ExecuteDataSet(CommandType.Text, sql);
        }
        /// <summary>
        /// 獲取地接社的‘操作号’
        /// </summary>
        /// <param name="p"></param>
        /// <returns></returns>
        public object GetOperationNumberForTravel(string tourGroupId, string ownerProviderId)
        {
            Database db = DatabaseFactory.CreateDatabase();

            DbCommand dbCommand;
            string sql = string.Format("SELECT OperationNumber FROM Sch_ProvidersPlanSchedule WHERE TourGroupID='{0}' AND OwnerProviderID='{1}'", tourGroupId, ownerProviderId);
            dbCommand = db.GetSqlStringCommand(sql);

            return db.ExecuteScalar(dbCommand);
        }
        /// <summary>
        /// 拷贝团备注到本地备注中
        /// </summary>
        /// <param name="tourGroupId"></param>
        /// <returns></returns>
        public int CopyTourGroupRemark2LocalRemark(string tourGroupId)
        {
            Database db = DatabaseFactory.CreateDatabase();
            string sql = string.Format(@"UPDATE orgt_TourGroup SET LocalRemark=Remark WHERE TourgroupID='{0}'", tourGroupId);
            DbCommand dbCommand = db.GetSqlStringCommand(sql);

            return db.ExecuteNonQuery(dbCommand);
        }
        public DataTable GetLocalPlanScheduleForBillPrint(string tourGroupID, int pageNumber, string ownerProviderId)
        {
            Database db = DatabaseFactory.CreateDatabase();
            string sql = string.Format("SELECT [Date], [Time], [Journeytype], [StartCity], [DestinationCity], [TrafficType], [Traffic], [Context],[Dining],[Hotel] FROM Sch_ProvidersPlanSchedule WHERE TourgroupID='{0}' AND PageNumber={1} AND OwnerProviderID='{2}' AND [Journeytype]<>'' order by [Date],[Time]", tourGroupID, pageNumber, ownerProviderId);
            DbCommand dbCommand = db.GetSqlStringCommand(sql);

            DataSet ds = db.ExecuteDataSet(dbCommand);
            if (ds.Tables.Count > 0)
                return ds.Tables[0];
            else
                return null;
        }
        public int GetLocalPlanSchedulePageNumbers(string tourgroupId)
        {
            Database db = DatabaseFactory.CreateDatabase();

            DbCommand dbCommand;
            string sql = string.Format("SELECT Max(PageNumber) FROM Sch_ProvidersPlanSchedule WHERE TourGroupID='{0}'", tourgroupId);
            dbCommand = db.GetSqlStringCommand(sql);

            object obj = db.ExecuteScalar(dbCommand);
            return obj == DBNull.Value ? 0 : int.Parse(obj.ToString());
        }
        public DataTable GetLocalPlanScheduleForPrint(string tourGroupID, int pageNumber, string ownerProviderId)
        {
            Database db = DatabaseFactory.CreateDatabase();
            string sql = string.Format("SELECT [Date], [Time], [Journeytype], [StartCity], [DestinationCity], [TrafficType], [Traffic], [Context],[Dining],[Hotel] FROM Sch_ProvidersPlanSchedule WHERE TourgroupID='{0}' AND PageNumber={1} AND OwnerProviderID='{2}' order by [Date],[Time]", tourGroupID, pageNumber, ownerProviderId);
            DbCommand dbCommand = db.GetSqlStringCommand(sql);

            DataSet ds = db.ExecuteDataSet(dbCommand);
            if (ds.Tables.Count > 0)
                return ds.Tables[0];
            else
                return null;
        }
        /// <summary>
        /// 根据行程计划号，更新行程计划数据
        /// </summary>
        /// <param name="scheduleData"></param>
        /// <returns></returns>
        public int UpdateLocalTourGroupSchedule(ProvidersPlanSchedule scheduleData)
        {
            Database db = DatabaseFactory.CreateDatabase();
            string sql = string.Format(@"UPDATE Sch_ProvidersPlanSchedule SET
                                        [TourgroupID]='{0}',
                                        [Date]='{1}',                                      [Time]='{2}',
                                        [StayOver]={3},                                    [Journeytype]='{4}',
                                        [StartCity]='{5}',                                 [DestinationCity]='{6}',
                                        [TrafficType]='{7}',                               [Traffic]='{8}',
                                        [TicketType]='{9}',                                [TravelAgencyName]='{10}',
                                        [Reception]='{11}',                                [Context]='{12}',
                                        [Dining]='{13}',                                   [Hotel]='{14}',
                                        [TravelAgencyID]={15},                             [HotelID]={16},
                                        [OperationNumber]='{17}'
                                        WHERE LocalPlanScheduleID={18}",
                                        scheduleData.TourgroupID,
                                        scheduleData.Date,
                                        scheduleData.Time,
                                        scheduleData.StayOver,
                                        scheduleData.Journeytype,
                                        scheduleData.StartCity,
                                        scheduleData.DestinationCity,
                                        scheduleData.TrafficType,
                                        scheduleData.Traffic,
                                        scheduleData.TicketType,
                                        scheduleData.TravelAgencyName,
                                        scheduleData.Reception,
                                        scheduleData.Context,
                                        scheduleData.Dining,
                                        scheduleData.Hotel,
                                        scheduleData.TravelAgencyID,
                                        scheduleData.HotelID,
                                        scheduleData.OperationNumber,
                                        scheduleData.LocalPlanScheduleID);
            return db.ExecuteNonQuery(CommandType.Text, sql);
        }

        /// <summary>
        /// 删除指定计划号行程数据数据
        /// </summary>
        /// <param name="tourGroupScheduleID"></param>
        /// <returns></returns>
        public int DeleteLocalTourGroupSchedule(int localPlanScheduleId)
        {
            Database db = DatabaseFactory.CreateDatabase();
            string sql = string.Format(@"DELETE FROM Sch_ProvidersPlanSchedule WHERE LocalPlanScheduleID={0}", localPlanScheduleId);
            return db.ExecuteNonQuery(CommandType.Text, sql);
        }

        /// <summary>
        /// 行程计划数据创建
        /// </summary>
        /// <param name="scheduleData"></param>
        /// <returns></returns>
        public int CreateLocalTourGroupSchedule(ProvidersPlanSchedule entity)
        {
            Database db = DatabaseFactory.CreateDatabase();
            string sql = string.Format(@"INSERT INTO Sch_ProvidersPlanSchedule([TourgroupID], [Date], [Time], [StayOver], [Journeytype], [StartCity],
                                                                           [DestinationCity], [TrafficType], [Traffic], [TicketType], [TravelAgencyName],
                                                                           [Reception], [Context], [Dining], [Hotel],[TravelAgencyID],[HotelID],[TourGroupScheduleID],
                                                                           [Seat],[PageNumber],[OwnerProviderID])
                                                                           VALUES('{0}', '{1}', '{2}', {3}, '{4}','{5}','{6}', '{7}', '{8}','{9}','{10}','{11}','{12}','{13}','{14}',{15},{16},{17},'{18}',{19},'{20}')",
                                                                            entity.TourgroupID,
                                                                            entity.Date,
                                                                            entity.Time,
                                                                            entity.StayOver,
                                                                            entity.Journeytype,
                                                                            entity.StartCity,
                                                                            entity.DestinationCity,
                                                                            entity.TrafficType,
                                                                            entity.Traffic,
                                                                            entity.TicketType,
                                                                            entity.TravelAgencyName,
                                                                            entity.Reception,
                                                                            entity.Context,
                                                                            entity.Dining,
                                                                            entity.Hotel,
                                                                            entity.TravelAgencyID,
                                                                            entity.HotelID,
                                                                            entity.TourGroupScheduleID,
                                                                            entity.Seat,
                                                                            entity.PageNumber,
                                                                            entity.OwnerProviderID);
            return db.ExecuteNonQuery(CommandType.Text, sql);
        }
        /// <summary>
        /// 更新地接社的‘操作号’
        /// </summary>
        /// <param name="scheduleData"></param>
        /// <returns></returns>
        public int UpdateOperationNumberForTravel(string tourgroupId, string operationNumber, string ownerProviderID)
        {
            Database db = DatabaseFactory.CreateDatabase();
            string sql = string.Format(@"UPDATE Sch_ProvidersPlanSchedule SET [OperationNumber]='{0}' WHERE TourgroupID='{1}' AND OwnerProviderID='{2}'", operationNumber, tourgroupId, ownerProviderID);
            return db.ExecuteNonQuery(CommandType.Text, sql);
        }
    }
}
